#!/bin/bash
#设置任务异常退出
set -eo pipefail
dt='{{ execution_date | cst_ds }}'
name='进港转运及时率明细'
# 在退出时清理文件
trap "rm -f /tmp/${name}_${dt}.zip /tmp/${name}_${dt}.csv && echo 'removed tmp files'" EXIT
# 创建txt文件
touch /tmp/${name}_${dt}.txt
#将中文字段名输入文件顶部
#将查询结果追加到文件中
{
  echo -e "发件时间\t是否及时\t兜底路由-是否及时\t订单来源编码\t订单来源名称\t物品类型编码\t运单号\t寄件代理区编码\t寄件代理区名称\t始发转运中心所属代理区编码\t始发转运中心所属代理区名称\t始发转运中心编码\t始发转运中心名称\t寄件网点编码\t寄件网点名称\t上一代理区编码\t上一代理区名称\t上一转运中心编码\t上一转运中心名称\t上一站编码\t上一站名称\t末端转运中心所属管理大区编码\t末端转运中心所属管理大区名称\t末端转运中心所属代理区编码\t末端转运中心所属代理区名称\t末端转运中心所属省份id\t末端转运中心所属省份名称\t末端转运中心编码\t末端转运中心名称\t下一站编码\t下一站名称\t派件代理区编码\t派件代理区名称\t签收省份id\t签收省份名称\t签收城市id\t签收城市名称\t签收区县id\t签收区县名称\t签收网点编码\t签收网点名称\t上一站发车任务编号\t末端中心到车任务编号\t到车任务编号\t到车任务名称\t计费方式\t计划到车时间\t实际到车时间\t到件时间\t到件包号\t判定到车到件时间\t发车任务编号\t发件包号\t应散货时间\t实际散货时间\t最晚到车任务编号\t最晚到车任务名称\t最晚到车任务号-计费方式\t最晚到车任务号-计划到车时间\t最晚到车任务号-实际到车时间\t最晚到车任务号-判定到车到件时间\t最晚到车任务号-应散货时间\t兜底路由-应散货时间\t发件日期\t衔接时长\t三段码\t到件时间的判断逻辑编码\t不及时原因\t兜底路由-不及时原因\t路由是否符合\t兜底路由是否符合\t时间分区\t">/tmp/${name}_${dt}.txt;
  spark-sql --driver-memory 8G --num-executors 8 --executor-memory 12g --executor-cores 6  --conf spark.executor.memoryOverhead=4g --conf spark.driver.maxResultSize=3g -S -e "
with tmp_waybill as (
select
waybill_no,
express_type_name
from jms_dwd.dwd_yl_oms_oms_waybill_dt
where dt >= date_add('{{ execution_date | cst_ds }}',-15) and dt <= date_add('{{ execution_date | cst_ds }}',1)
and express_type_name = '兔优达'
)

select
 if(t1.send_time                       = '', null, t1.send_time                     ) as send_time
,if(t1.is_timely                       = '', null, t1.is_timely                     ) as is_timely
,if(t1.route_is_timely                 = '', null, t1.route_is_timely               ) as route_is_timely
,if(t1.order_source_code               = '', null, t1.order_source_code             ) as order_source_code
,if(t1.order_source_name               = '', null, t1.order_source_name             ) as order_source_name
,if(t1.goods_type_code                 = '', null, t1.goods_type_code               ) as goods_type_code
,if(t1.billcode                        = '', null, t1.billcode                      ) as billcode
,if(t1.pick_agent_code                 = '', null, t1.pick_agent_code               ) as pick_agent_code
,if(t1.pick_agent_name                 = '', null, t1.pick_agent_name               ) as pick_agent_name
,if(t1.start_center_agent_code         = '', null, t1.start_center_agent_code       ) as start_center_agent_code
,if(t1.start_center_agent_name         = '', null, t1.start_center_agent_name       ) as start_center_agent_name
,if(t1.start_center_code               = '', null, t1.start_center_code             ) as start_center_code
,if(t1.start_center_name               = '', null, t1.start_center_name             ) as start_center_name
,if(t1.pick_network_code               = '', null, t1.pick_network_code             ) as pick_network_code
,if(t1.pick_network_name               = '', null, t1.pick_network_name             ) as pick_network_name
,if(t1.last_agent_code                 = '', null, t1.last_agent_code               ) as last_agent_code
,if(t1.last_agent_name                 = '', null, t1.last_agent_name               ) as last_agent_name
,if(t1.last_center_code                = '', null, t1.last_center_code              ) as last_center_code
,if(t1.last_center_name                = '', null, t1.last_center_name              ) as last_center_name
,if(t1.last_station_code               = '', null, t1.last_station_code             ) as last_station_code
,if(t1.last_station_name               = '', null, t1.last_station_name             ) as last_station_name
,if(t1.end_center_manage_region_code   = '', null, t1.end_center_manage_region_code ) as end_center_manage_region_code
,if(t1.end_center_manage_region_name   = '', null, t1.end_center_manage_region_name ) as end_center_manage_region_name
,if(t1.end_center_agent_code           = '', null, t1.end_center_agent_code         ) as end_center_agent_code
,if(t1.end_center_agent_name           = '', null, t1.end_center_agent_name         ) as end_center_agent_name
,if(t1.end_center_provider_id          = '', null, t1.end_center_provider_id        ) as end_center_provider_id
,if(t1.end_center_provider_name        = '', null, t1.end_center_provider_name      ) as end_center_provider_name
,if(t1.end_center_code                 = '', null, t1.end_center_code               ) as end_center_code
,if(t1.end_center_name                 = '', null, t1.end_center_name               ) as end_center_name
,if(t1.next_station_code               = '', null, t1.next_station_code             ) as next_station_code
,if(t1.next_station_name               = '', null, t1.next_station_name             ) as next_station_name
,if(t1.deliver_agent_code              = '', null, t1.deliver_agent_code            ) as deliver_agent_code
,if(t1.deliver_agent_name              = '', null, t1.deliver_agent_name            ) as deliver_agent_name
,if(t1.sign_provider_id                = '', null, t1.sign_provider_id              ) as sign_provider_id
,if(t1.sign_provider_name              = '', null, t1.sign_provider_name            ) as sign_provider_name
,if(t1.sign_city_id                    = '', null, t1.sign_city_id                  ) as sign_city_id
,if(t1.sign_city_name                  = '', null, t1.sign_city_name                ) as sign_city_name
,if(t1.sign_area_id                    = '', null, t1.sign_area_id                  ) as sign_area_id
,if(t1.sign_area_name                  = '', null, t1.sign_area_name                ) as sign_area_name
,if(t1.sign_network_code               = '', null, t1.sign_network_code             ) as sign_network_code
,if(t1.sign_network_name               = '', null, t1.sign_network_name             ) as sign_network_name
,if(t1.last_station_send_shipment_no   = '', null, t1.last_station_send_shipment_no ) as last_station_send_shipment_no
,if(t1.end_center_arrival_shipment_no  = '', null, t1.end_center_arrival_shipment_no) as end_center_arrival_shipment_no
,if(t1.arrival_shipment_no             = '', null, t1.arrival_shipment_no           ) as arrival_shipment_no
,if(t1.arrival_shipment_name           = '', null, t1.arrival_shipment_name         ) as arrival_shipment_name
,if(t1.billing_way                     = '', null, t1.billing_way                   ) as billing_way
,if(t1.plan_arrival_time               = '', null, t1.plan_arrival_time             ) as plan_arrival_time
,if(t1.actual_arrival_time             = '', null, t1.actual_arrival_time           ) as actual_arrival_time
,if(t1.arrival_scan_time               = '', null, t1.arrival_scan_time             ) as arrival_scan_time
,if(t1.arrival_package_code            = '', null, t1.arrival_package_code          ) as arrival_package_code
,if(t1.arrival_time                    = '', null, t1.arrival_time                  ) as arrival_time
,if(t1.send_shipment_no                = '', null, t1.send_shipment_no              ) as send_shipment_no
,if(t1.send_package_code               = '', null, t1.send_package_code             ) as send_package_code
,if(t1.cargo_time                      = '', null, t1.cargo_time                    ) as cargo_time
,if(t1.actual_cargo_time               = '', null, t1.actual_cargo_time             ) as actual_cargo_time
,if(t1.lst_arrival_shipment_no         = '', null, t1.lst_arrival_shipment_no       ) as lst_arrival_shipment_no
,if(t1.lst_arrival_shipment_name       = '', null, t1.lst_arrival_shipment_name     ) as lst_arrival_shipment_name
,if(t1.lst_billing_way                 = '', null, t1.lst_billing_way               ) as lst_billing_way
,if(t1.lst_planned_arrival_time        = '', null, t1.lst_planned_arrival_time      ) as lst_planned_arrival_time
,if(t1.lst_actual_arrival_time         = '', null, t1.lst_actual_arrival_time       ) as lst_actual_arrival_time
,if(t1.lst_arrival_time                = '', null, t1.lst_arrival_time              ) as lst_arrival_time
,if(t1.lst_cargo_time                  = '', null, t1.lst_cargo_time                ) as lst_cargo_time
,if(t1.route_cargo_time                = '', null, t1.route_cargo_time              ) as route_cargo_time
,if(t1.send_date                       = '', null, t1.send_date                     ) as send_date
,if(t1.reserved_time                   = '', null, t1.reserved_time                 ) as reserved_time
,if(t1.terminal_dispatch_code          = '', null, replace(t1.terminal_dispatch_code,',','_')) as terminal_dispatch_code
,if(t1.logic_type                      = '', null, t1.logic_type                    ) as logic_type
,if(t1.untimely_type                   = '', null, t1.untimely_type                 ) as untimely_type
,if(t1.route_untimely_type             = '', null, t1.route_untimely_type           ) as route_untimely_type
,if(t1.is_route                        = '', null, t1.is_route                      ) as is_route
,if(t1.is_route_doudi                  = '', null, t1.is_route_doudi                ) as is_route_doudi
,if(t1.dt                              = '', null, t1.dt                            ) as dt
from jms_dwd.dwd_centersend_timerate_detail_dt t1
join tmp_waybill t2
  on t1.billcode = t2.waybill_no
where dt = '{{ execution_date | date_add(-1) | cst_ds }}'
;
 " >>/tmp/${name}_${dt}.txt
}

# 替换分隔符，并输出csv
cat /tmp/${name}_${dt}.txt | tr -s '\t' ',' > /tmp/${name}_${dt}.csv
# 压缩并删除 csv 文件
zip -jrm /tmp/${name}_${dt}.zip /tmp/${name}_${dt}.csv
# 上传文件
/usr/local/python3/bin/python3  /usr/local/airflow/dags/utils/operators/upload_oss.py ${name}/${name}_${dt}.zip /tmp/${name}_${dt}.zip
# zip 文件将在进程退出后被自动清理

#声明文件大小
data_size=$(ls -lh /tmp/${name}_${dt}.zip | awk '{print $5}')
#打印文件信息
Message="${name}_${dt}.zip 上传成功 大小: $data_size"
#配置钉钉告警
curl 'https://oapi.dingtalk.com/robot/send?access_token=816a8f35fd84432ffa6bff9d7714abc322628bb4a8ded86908550d2335aba613' \
   -H 'Content-Type: application/json' \
   -d "
  {\"msgtype\": \"text\",
    \"text\": {
        \"content\": \"监控内容:$Message\"
     }
  }" || true
rm -f  /tmp/${name}_${dt}.txt